﻿using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;

namespace Utility
{
    public class ExcelForSearch
    {
        public static ExcelForSearch Instance = new ExcelForSearch();

        private ExcelForSearch() { }

        private HSSFWorkbook workbook;

        private HSSFWorkbook InitWorkbook()
        {
            workbook = new HSSFWorkbook();
            return workbook;
        }

        private HSSFSheet InitSheet(HSSFWorkbook workbook, string sheetName)
        {
            return workbook.CreateSheet(sheetName);
        }

        private HSSFCell InitCell(HSSFRow row, int col, HSSFCellStyle style)
        {
            HSSFCell cell = row.CreateCell(col);
            if (style != null)
            {
                cell.CellStyle = style;
            }
            return cell;
        }

        private void SaveExcel(string filePath)
        {
            FileStream file = null;
            filePath = filePath.Replace("\\", "/");
            string path = filePath.Substring(0, filePath.LastIndexOf("/") + 1);
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            file = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
            workbook.Write(file);
            file.Close();
        }

        private void DataTableInsert(DataTable dt, HSSFWorkbook workbook, HSSFSheet sheet)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int col = 0;
                HSSFRow row = sheet.CreateRow(i + 1);
                foreach (DataColumn dc in dt.Columns)
                {
                    string colName = dc.ColumnName;

                    HSSFCell cell = InitCell(row, col++, null);

                    cell.SetCellValue(dt.Rows[i][colName].ToString());
                    //sheet.AutoSizeColumn(col++, true);
                    //int width = dt.Rows[i][colName].ToString().Length;
                    //sheet.SetColumnWidth(col++, width == 0 ? 1 : width * 256 * 3);
                }
            }
        }

        private void CreateHeader(DataTable dt, HSSFSheet excelSheet)
        {
            int cellIndex = 0;
            //循环导出列
            HSSFRow newRow = excelSheet.CreateRow(0);
            foreach (DataColumn dc in dt.Columns)
            {
                HSSFCell newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(dc.ColumnName);

                excelSheet.SetColumnWidth(cellIndex++, dc.ColumnName.Length * 256 * 3);
            }
        }

        public bool ExportToExcel(DataTable dt, string path, string sheetName = "查询报表")
        {
            try
            {
                HSSFWorkbook workbook = InitWorkbook();

                HSSFSheet sheet = InitSheet(workbook, sheetName);

                CreateHeader(dt, sheet);
                DataTableInsert(dt, workbook, sheet);
                SaveExcel(path);
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }
    }
}